<?php

namespace ai\db;

use YII;
use yii\db\Command;
use yii\db\Connection;
use yii\db\Query;

class TablesBase
{

    /**
     * @var TablesBaseQuery
     */
    public $query;
    public $tableName;

    function __construct($table)
    {
        $this->tableName = $table;


        $cmd = new \yii\db\Query();
        $cmd = $cmd->from($table);
        $this->query = new TablesBaseQuery($cmd);
    }

    public function insert($data)
    {
        $cmd = Yii::$app->db->createCommand()->insert($this->tableName, $data);
        return new TablesBaseIUD($cmd, 'insert');
    }

    public function update($data, $where = '')
    {
        $cmd = Yii::$app->db->createCommand()->update($this->tableName, $data, $where);
        return new TablesBaseIUD($cmd);
    }

    public function delete($where)
    {
        $cmd = Yii::$app->db->createCommand()->delete($this->tableName, $where);
        return new TablesBaseIUD($cmd);
    }

    public function insertBatch($fields, $data)
    {
        $sql = "INSERT INTO {$this->tableName} ";

        //处理字段：
        if (is_string($fields)) {
            $trueFields = explode(',', $fields);
        } else {
            $trueFields = $fields;
        }

        $sql .= "(".implode(',', $trueFields).")";
        //处理数据：
        $valueService = [];
        $field = [];
        if (is_array($data) && !empty($data)) {
            foreach ($data as $v) {
                $value = [];
                foreach ($trueFields as $key) {
                    if (isset($v[$key])) {
                        if (is_int($v[$key])) {
                            array_push($value, "{$v[$key]}");
                        } else {
                            array_push($value, "'{$v[$key]}'");
                        }
                    }
                }
                if (!empty($value)) {
                    $valueService[] = "(" . implode(',', $value) . ")";
                }
            }
        }

        if (!empty($valueService)) {
            $valueNew = implode(',', $valueService);
            $sql .= " VALUES " . $valueNew;
        }
        $cmd = Yii::$app->db->createCommand($sql);

        return new TablesBaseIUD($cmd);
    }

    public function updateAllCounters($counters, $where)
    {
        $condition = [];
        $sql = "UPDATE {$this->tableName}";
        foreach ($counters as $k => $v) {
            if (is_numeric($v)) {
                if ($v < 0) {
                    $flag = '-';
                } else {
                    $flag = '+';
                }
                $v = abs($v);
                $condition[] = "{$k}={$k} {$flag} {$v}";
            }
        }

        $andWhere = '';
        if (!empty($where) && is_array($where)) {
            $temp = [];
            foreach ($where as $k => $v) {
                $temp[] = "{$k}='{$v}'";
            }
            if (!empty($temp)) {
                $andWhere = "WHERE " . implode(' and ', $temp);
            }

        } else {
            $andWhere = "WHERE " . $where;
        }

        if (!empty($condition)) {
            $sql .= " set " . implode(' , ', $condition) . " " . $andWhere;
        }
        $cmd = Yii::$app->db->createCommand($sql);

        return new TablesBaseIUD($cmd);

    }


}

class TablesBaseIUD
{
    /**
     * @var  Command
     */
    private $cmd;
    private $type;

    function __construct($cmd, $type = '')
    {
        $this->cmd = $cmd;
        $this->type = $type;
    }

    function sql()
    {
        return $this->cmd->rawSql;
    }

    function execute()
    {
        $row = $this->cmd->execute();
        if (empty($this->type))
            return $row;
        else
            return YII::$app->db->getLastInsertId();
    }
}

class TablesBaseQuery
{
    /**
     * @var Query
     */
    private $cmd;

    function __construct($cmd, $type = '')
    {
        $this->cmd = $cmd;
    }

    function select($columns)
    {
        $cmd = $this->cmd;
        $cmd->select($columns);
        $this->cmd = $cmd;
        return $this;
    }

    function distinct($columns)
    {
        $cmd = $this->cmd;
        $cmd->distinct($columns);
        $this->cmd = $cmd;
        return $this;
    }

    function where($condition, $params = [])
    {
        $cmd = $this->cmd;
        $cmd->where($condition);
        if (!empty($params))
            $cmd->where($condition, $params);
        $this->cmd = $cmd;
        return $this;
    }
    function orWhere($condition, $params = [])
    {
        $cmd = $this->cmd;
        $cmd->orWhere($condition);
        if (!empty($params))
            $cmd->orWhere($condition, $params);
        $this->cmd = $cmd;
        return $this;
    }

    function andWhere($condition, $params = [])
    {
        $cmd = $this->cmd;
        $cmd->andWhere($condition);
        if (!empty($params))
            $cmd->andWhere($condition, $params);
        $this->cmd = $cmd;
        return $this;
    }

    function order($columns)
    {
        $cmd = $this->cmd;
        $cmd->orderBy($columns);
        $this->cmd = $cmd;
        return $this;
    }

    function groupBy($columns)
    {
        $cmd = $this->cmd;
        $cmd->groupBy($columns);
        $this->cmd = $cmd;
        return $this;
    }


    function limit($limit)
    {
        $cmd = $this->cmd;
        $cmd->limit($limit);
        $this->cmd = $cmd;
        return $this;
    }

    public function offset($offset)
    {
        $cmd = $this->cmd;
        $cmd->offset($offset);
        $this->cmd = $cmd;
        return $this;
    }


    function one()
    {
        return $this->cmd->one();
    }

    public function count($q = '*')
    {
        return $this->cmd->count($q);
    }

    function all()
    {
        return $this->cmd->all();
    }

    public function sql()
    {
        return $this->cmd->createCommand()->getRawSql();
    }

    function paging($pageIndex, $pageCount)
    {
        $result = ['total' => 0, 'data' => []];

        $cmd = $this->cmd;

        $select = $cmd->select;

        $cmd->select("count(*) as num");

        $countRow = $cmd->one();
        if (isset($countRow['num']))
            $result['total'] = (int)$countRow['num'];

        $cmd->select($select);

        $offset = ($pageIndex - 1) * $pageCount;

        $cmd->offset($offset);
        $cmd->limit($pageCount);

        $result['data'] = $cmd->all();
        $result['size'] = (int)$pageCount;
        $result['pages'] = ceil($result['total'] / $pageCount);
        $result['index'] = (int)$pageIndex;

        return $result;
    }
}
